const sql = require('mssql');
const baseFn = require('../../public/base.js');


/**
 * 请款单审核
 *  */


// 获取付款单信息
const getVerifyInvoiceOrder = async (ctx) => {
    const { index, pageSize, searchList } = ctx.request.body;
    let publicSql1 = ` FROM FKD_ZB Z LEFT JOIN (SELECT SUM(QM.FLJE) AS ZFLJE,QZ.ID_FKD FROM QKD_MX QM LEFT JOIN QKD_ZB QZ ON QM.ID = QZ.ID 
    GROUP BY QZ.ID_FKD) FL ON Z.ID = FL.ID_FKD LEFT JOIN  (SELECT ID_FKD,JLSP,CWSP,ZJLSP,CWHX,ISNULL(HXJE,0) AS HXJE,FKZH,FKR,FKRQ,CWHXBZ,JLSPRQ,CWSPRQ,ZJLSPRQ,JLSHR,CWSHR,ZJLSHR,CWHXR,CWHXRQ  
    FROM QKD_ZB GROUP BY ID_FKD,JLSP,CWSP,ZJLSP,CWHX,FKRQ,HXJE,FKZH,FKR,FKRQ,CWHXBZ,JLSPRQ,CWSPRQ,ZJLSPRQ,JLSHR,CWSHR,ZJLSHR,CWHXR,CWHXRQ) SP ON Z.ID = SP.ID_FKD
     LEFT JOIN BM_WLDW W ON Z.DWBM = W.BM WHERE 1=1 `;

    let totalSql = `SELECT COUNT(Z.ID) AS num ${publicSql1} `;
    //1是未审批，2是经理已审批，3是财务已审批，4待核销，5、已核销
    let selectSql = `SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY Z.ID) AS px, CASE WHEN SP.JLSP = 'F' THEN 1 WHEN SP.CWSP = 'F' THEN 2 WHEN SP.ZJLSP = 'F' 
    THEN 3 WHEN SP.CWHX = 'F' THEN 4 ELSE 5 END AS SPZT,ISNULL(FL.ZFLJE,0) AS ZFLJE,Z.ID,Z.RQ,Z.DWMC,Z.JBRMC,Z.JSFSMC,Z.ZJE,Z.TJE,Z.YFE,Z.EJS,Z.WJS,
    SP.HXJE,SP.FKZH,SP.FKR,SP.FKRQ,SP.CWHXBZ,SP.JLSPRQ,SP.CWSPRQ,SP.ZJLSPRQ,SP.JLSHR,SP.CWSHR,SP.ZJLSHR,SP.CWHXR,SP.CWHXRQ ${publicSql1}`;
    let searchSqlList = [];
    if (searchList.times[0]) {
        searchSqlList.push(` AND Z.RQ >= '${searchList.times[0]} 00:00:00' AND Z.RQ <= '${searchList.times[1]} 23:59:59' `);
    }
    if (searchList.GYS) {
        searchSqlList.push(` AND (W.BM like '%${searchList.GYS}%' OR W.MC like '%${searchList.GYS}%' OR W.JP like '%${searchList.GYS}%' ) `);
    }
    if (searchList.SFSH) {
        searchSqlList.push(` AND SP.JLSP = '${searchList.SFSH}' `);
    }
    if (searchList.SFFL) {
        searchSqlList.push(` AND Z.ID ${searchList.SFFL === 'F' ? 'NOT' : ''} IN (SELECT QZ.ID_FKD FROM QKD_MX QM LEFT JOIN QKD_ZB QZ ON QM.ID = QZ.ID WHERE ISNULL(QM.FLFS,'') != '' AND QM.FLJE > 0) `);

    }
    if (searchList.cancelTimes[0]) {
        searchSqlList.push(` AND SP.FKRQ >= '${searchList.cancelTimes[0]} 00:00:00' AND SP.FKRQ <= '${searchList.cancelTimes[1]} 23:59:59' `);

    }
    if (searchList.isCancel) {
        searchSqlList.push(` AND SP.CWHX = '${searchList.isCancel}' `);

    }

    totalSql += searchSqlList.join(' ');
    selectSql += ` ${searchSqlList.join(' ')}) T1 WHERE T1.PX between ${(index - 1) * pageSize + 1} and ${index * pageSize}`;
    let listResult = await sql.query(selectSql);
    let totalResult = await sql.query(totalSql);
    let result = listResult.recordset;
    let total = totalResult.recordset[0].num;
    ctx.body = { result, total }
}

// 获取付款单详情的信息
const getVerifyInvoiceOrderDetail = async (ctx) => {
    const { id } = ctx.request.body;
    let selectSql = `SELECT M.DJID,M.DJRQ,M.DJLX,M.YJS,M.EJS,M.WJS,M.JBRMC,QZ.FPH,QZ.FLJE,QZ.TJE,QZ.ID AS QKID FROM FKD_MX M LEFT JOIN QKD_ZB QZ 
    ON M.ID = QZ.ID_FKD AND M.DJID = QZ.ID_GL WHERE M.ID = '${id}'`;
    let selectResult = await sql.query(selectSql);
    let lastResult = selectResult.recordset;
    for (let item of lastResult) {
        if (item.DJLX === 'DHD') {
            let selectDhdSql = ` SELECT DZ.ZSL AS CGSL,DZ.ZJE AS CGJE,RK.RKSL,RK.RKJE FROM DHD_ZB DZ LEFT JOIN (SELECT SUM(ZSL) AS RKSL,
            SUM(ZJE) AS RKJE,ID_DHD FROM RKD_ZB GROUP BY ID_DHD) AS RK ON RK.ID_DHD = DZ.ID  WHERE ID = '${item.DJID}' `;
            let dhdResult = await sql.query(selectDhdSql);
            if (dhdResult.rowsAffected[0] > 0) {
                item.CGSL = dhdResult.recordset[0].CGSL
                item.CGJE = dhdResult.recordset[0].CGJE
                item.RKSL = dhdResult.recordset[0].RKSL
                item.RKJE = dhdResult.recordset[0].RKJE
            }
        }
    }

    ctx.body = { result: selectResult.recordset }
}

// 获取请款单商品的详情信息
const getVerifyInvoiceShopDetail = async (ctx) => {
    const { id } = ctx.request.body;
    let selectSql = `SELECT M.YPBM,M.YPMC,M.YPGG,M.SCCJ,M.JLDW,M.PZWH,M.DJ,M.SL,M.JE,M.KPDJ,M.FLFS,M.FLQX,M.FLZS,M.PPFL,M.FLTJ,M.FLJE,QZ.DJLX,QZ.ID_GL FROM QKD_MX M 
    LEFT JOIN QKD_ZB QZ ON M.ID = QZ.ID  WHERE M.ID = '${id}'`;

    let selectResult = await sql.query(selectSql);
    for (let item of selectResult.recordset) {
        if (item.DJLX === 'DHD') {
            let selectTotalSql = ` SELECT SUM(RM.SL) AS RKSL,SUM(RM.JE) AS RKJE FROM RKD_MX RM LEFT JOIN RKD_ZB RZ ON RM.ID = RZ.ID WHERE RM.YPBM = '${item.YPBM}' AND RZ.ID_DHD = '${item.ID_GL}' GROUP BY RM.YPBM `;
            let totalResult = await sql.query(selectTotalSql);

            let lastTotalResult = totalResult.recordset;

            item.RKSL = 0 in lastTotalResult ? totalResult.recordset[0].RKSL : 0;
            item.RKJE = 0 in lastTotalResult ? totalResult.recordset[0].RKJE : 0;
            let selectShopSql = ` SELECT RM.YPBM,RM.YPMC,RM.YPGG,RM.JXMC,RM.SCCJ,RM.PZWH,RM.SCPH,RM.JLDW,RM.SL,RM.DJ,RM.JE,RZ.RQ,RZ.ID,RZ.JBRMC
            FROM RKD_MX RM LEFT JOIN RKD_ZB RZ ON RM.ID = RZ.ID WHERE RM.YPBM = '${item.YPBM}' AND RZ.ID_DHD = '${item.ID_GL}' `;
            let shopResult = await sql.query(selectShopSql);
            item.rkShopDetail = shopResult.recordset

        }
    }
    selectResult.recordset
    ctx.body = { result: selectResult.recordset }
}

// 判断是谁审批的
function judgeVerifyType(type, BZ, SHR) {
    let nowTime = baseFn.getNowTimeId().halfDate;
    const typeList = {
        manager: `JLSP = 'T',JLBZ='${BZ}',JLSPRQ='${nowTime}',JLSHR='${SHR}'`,
        finance: `CWSP = 'T',CWBZ='${BZ}',CWSPRQ='${nowTime}',CWSHR='${SHR}'`,
        generalManager: `ZJLSP = 'T',ZJLBZ='${BZ}',ZJLSPRQ='${nowTime}',ZJLSHR='${SHR}'`
    }
    return typeList[type]
}
// 获取请款单商品的详情信息
const keepVerifyInvoice = async (ctx) => {
    try {
        const { id, type, BZ, SHR } = ctx.request.body;
        let updateQkdSql = `UPDATE QKD_ZB SET ${judgeVerifyType(type, BZ, SHR)} WHERE ID_FKD = '${id}'`;
        let updateQkdResult = await sql.query(updateQkdSql);
        if (updateQkdResult.rowsAffected[0] > 0) {
            ctx.body = { success: true }
        } else {
            ctx.body = { success: false }
        }

    } catch (err) {
        ctx.body = { success: false, err }
    }
}
// 请款单财务核销保存
const keepVerifyInvoiceCancel = async (ctx) => {
    // try {
        const { ZFJE, FKZH, FKR, FKRQ, ID, BZ, SHR } = ctx.request.body;
        let nowTime = baseFn.getNowTimeId().halfDate;
        // 保存要执行的sql

        let needPayResult = await sql.query(` SELECT YFE FROM FKD_ZB WHERE ID = '${ID}' `);
        let needPay = Number(needPayResult.recordset[0].YFE ? needPayResult.recordset[0].YFE : 0);
        let payResult = await sql.query(` SELECT HXJE FROM QKD_ZB WHERE ID_FKD = '${ID}' `);
        let pay = Number(payResult.recordset[0].HXJE ? payResult.recordset[0].HXJE : 0);
        let totalPay = pay + Number(ZFJE);

        if (needPay < totalPay) {
            ctx.body = { success: false, message: '核销金额不能大于应付金额' }
        } else if (needPay > totalPay) {
            let updateQkdSql = `UPDATE QKD_ZB SET HXJE = ${totalPay},FKZH = '${FKZH}',FKR='${FKR}',FKRQ = '${FKRQ}',CWHXBZ = '${BZ}',CWHXR='${SHR}',CWHXRQ='${nowTime}' WHERE ID_FKD = '${ID}'`;
            let lastResult = await sql.query(updateQkdSql);
            if (lastResult.rowsAffected[0] > 0) {
                ctx.body = { success: true }
            } else {
                ctx.body = { success: false }
            }
        }
        else if (needPay === totalPay) {
            let actionSqlList = [];
            // 更新付款单的状态
            let updateFkdSql = `UPDATE FKD_ZB SET SH = 'T' WHERE ID = '${ID}'`;
            actionSqlList.push(updateFkdSql);

            // 支付信息写入请款单
            let updateQkdSql = `UPDATE QKD_ZB SET HXJE = ${totalPay},FKZH = '${FKZH}',FKR='${FKR}',FKRQ = '${FKRQ}',CWHX ='T',CWHXBZ = '${BZ}',CWHXR='${SHR}',CWHXRQ='${nowTime}' WHERE ID_FKD = '${ID}'`;
            actionSqlList.push(updateQkdSql);

            let orderInfoSql = `SELECT QZ.ID_GL,QZ.DJLX,QZ.BCFK,QZ.TJE,QZ.DWBM FROM QKD_ZB QZ WHERE ID_FKD = '${ID}'`;



            let orderInfoResult = await sql.query(orderInfoSql);
            let orderInfo = orderInfoResult.recordset;

            let jsfsbm = '';
            // 更新对应单据类型的总表
            if (0 in orderInfo) {
                for (let item of orderInfo) {
                    let billInforSql = `SELECT YJS,EJS,${item.DJLX !== 'DHD' ? 'TJE,' : ''}JSFSBM FROM ${item.DJLX}_ZB WHERE ID = '${item.ID_GL}'`;
                    let billInforResult = await sql.query(billInforSql);
                    let billInfo = billInforResult.recordset[0];
                    jsfsbm = billInfo.JSFSBM;
                    let yjs = Number(billInfo.YJS ? billInfo.YJS : 0);
                    let ejs = Number(billInfo.EJS ? billInfo.EJS : 0) + item.BCFK;
                    let tje = Number(billInfo.TJE ? billInfo.TJE : 0);
                    let wjs = yjs - ejs - tje;
                    let updateBillSql = `UPDATE ${item.DJLX}_ZB SET EJS = ${ejs},WJS = ${wjs} WHERE ID = '${item.ID_GL}'`;
                    actionSqlList.push(updateBillSql);
                }
            }
            // 更新对应供应商的预付额
            if (orderInfo[0].DJLX === 'DHD' && jsfsbm === '0') {
                let fkdZbInforResult = await sql.query(`SELECT YFE,TJE FROM FKD_ZB WHERE ID = '${ID}'`);
                let fkdZbinfor = fkdZbInforResult.recordset[0];
                let dwInfoSql = `SELECT YUFYE,WLYE FROM BM_WLDW WHERE BM =  '${orderInfo[0].DWBM}'`;
                let dwInfoResult = await sql.query(dwInfoSql);
                let getYufye = dwInfoResult.recordset[0].YUFYE;
                let getWlye = dwInfoResult.recordset[0].WLYE;
                let yufye = Number(getYufye ? getYufye : 0) + fkdZbinfor.YFE - fkdZbinfor.TJE;
                let wlye = Number(getWlye ? getWlye : 0) + fkdZbinfor.YFE - fkdZbinfor.TJE;
                let updateWldwSql = `UPDATE BM_WLDW SET YUFYE = ${yufye},WLYE = ${wlye} WHERE BM =  '${orderInfo[0].DWBM}' `;
                actionSqlList.push(updateWldwSql);
            }
            let lastSql = `SET XACT_ABORT ON Begin Tran ${actionSqlList.join(' ')}  Commit Tran `;


            let lastResult = await sql.query(lastSql);
            if (lastResult.rowsAffected[0] > 0) {
                ctx.body = { success: true }
            } else {
                ctx.body = { success: false }
            }
        }

    // } catch (err) {
    //     ctx.body = { success: false, err }
    // }
}

// 获取请款单商品的详情信息
const getPrintVerifyInvoiceInfo = async (ctx) => {
    try {
        const { ID } = ctx.request.body;
        let selectStoreSql = ` SELECT FZ.RQ,FZ.ID AS FKDID,FZ.JSFSMC,DW.KHYH,DW.YHZH,FZ.DWMC,FZ.ZJE,FZ.JBRMC FROM FKD_ZB FZ LEFT JOIN BM_WLDW DW ON 
        FZ.DWBM = DW.BM WHERE FZ.ID = '${ID}' `;
        let selectOrderSql = ` SELECT QZ.FLJE,QZ.ID_GL,QZ.YJS,QZ.EJS,QZ.WJS,QZ.BCFK,QZ.TJE,QZ.FPH,QZ.DJLX FROM QKD_ZB QZ `;

        selectOrderSql += ` LEFT JOIN FKD_ZB FZ ON QZ.ID_FKD = FZ.ID WHERE FZ.ID = '${ID}' `;
        let selectShopSql = ` SELECT QM.YPMC,QM.YPGG,QM.JLDW,QM.SCCJ,QM.PZWH,QM.DJ,QM.JE,QM.SL,QM.FLFS,QM.FLJE,QM.FLQX,QM.PPFL FROM QKD_MX QM 
        LEFT JOIN QKD_ZB QZ ON QM.ID = QZ.ID LEFT JOIN FKD_ZB FZ ON QZ.ID_FKD = FZ.ID WHERE FZ.ID = '${ID}' AND ISNULL(QM.FLFS,'') != '' `;
        let storeResult = await sql.query(selectStoreSql);
        let orderResult = await sql.query(selectOrderSql);
        let shopResult = await sql.query(selectShopSql);
        let orderList = orderResult.recordset;
        if (0 in orderList) {
            for (let item of orderList) {
                let orderInfo = await sql.query(` SELECT RZ.JBRMC,RZ.JSFSMC FROM ${item.DJLX}_ZB RZ WHERE  RZ.ID = '${item.ID_GL}' `);
                let orderInfoResult = orderInfo.recordset[0];
                item.JSFSMC = orderInfoResult.JSFSMC;
                item.JBRMC = orderInfoResult.JBRMC;
            }
        }
        if (storeResult.rowsAffected[0] > 0 && orderResult.rowsAffected[0] > 0) {
            ctx.body = {
                success: true,
                storeResult: storeResult.recordset[0],
                orderResult: orderList,
                shopResult: shopResult.recordset
            }
        } else {
            ctx.body = { success: false }
        }

    } catch (err) {
        ctx.body = { success: false, err }
    }
}


// 获取要导出的返利商品
const getInvoiceExportData = async (ctx) => {

    try {
        const { searchList } = ctx.request.body;
        let selectSql = ` SELECT QZ.DJLX,QZ.ID_GL,QZ.FKRQ,QM.YPBM,QM.YPMC,QM.YPGG,QM.SCCJ,QM.PZWH,QM.JLDW,QM.FLFS,QM.FLQX,QM.PPFL,QM.FLZS,QM.FLTJ,QM.FLJE,QM.SL,QM.SCPH,QM.DJ,QZ.RQ FROM 
        QKD_MX QM LEFT JOIN QKD_ZB QZ ON QM.ID = QZ.ID LEFT JOIN BM_WLDW W ON QZ.DWBM = W.BM WHERE ISNULL(QM.FLFS,'') != '' AND QM.FLJE>0 AND QZ.RQ >= '${searchList.times[0]} 00:00:00' AND QZ.RQ <= '${searchList.times[1]} 23:59:59' `;
        let searchSqlList = [];
        if (searchList.GYS) {
            searchSqlList.push(` AND (W.BM like '%${searchList.GYS}%' OR W.MC like '%${searchList.GYS}%' OR W.JP like '%${searchList.GYS}%' ) `);
        }
        if (searchList.SFSH) {
            searchSqlList.push(` AND QZ.JLSP = '${searchList.SFSH}' `);
        }
        if (searchList.cancelTimes[0]) {
            searchSqlList.push(` AND QZ.FKRQ >= '${searchList.cancelTimes[0]} 00:00:00' AND QZ.FKRQ <= '${searchList.cancelTimes[1]} 23:59:59' `);

        }
        if (searchList.isCancel) {
            searchSqlList.push(` AND QZ.CWHX = '${searchList.isCancel}' `);

        }
        selectSql += ` ${searchSqlList.join(' ')} `;
        let result = await sql.query(selectSql);
        if (result.rowsAffected[0] > 0) {
            for (let item of result.recordset) {
                let cgr = '';
                if (item.DJLX === 'RKD') {
                    cgr = await sql.query(` SELECT DZ.JBRMC,DZ.DWMC,DZ.DWBM FROM RKD_ZB RZ LEFT JOIN DHD_ZB DZ ON RZ.ID_DHD = DZ.ID WHERE RZ.ID = '${item.ID_GL}' `);

                } else {
                    cgr = await sql.query(` SELECT DZ.JBRMC,DZ.DWMC,DZ.DWBM FROM DHD_ZB DZ  WHERE DZ.ID = '${item.ID_GL}' `);
                }
                let flxx = await sql.query(` SELECT YG.MC FROM FLSZ FL LEFT JOIN BM_YG YG ON FL.JBR = YG.BM WHERE GYSBM = '${cgr.recordset[0].DWBM}' AND YPBM = '${item.YPBM}' `);
                item.CGR = cgr.recordset[0].JBRMC
                item.GYS = cgr.recordset[0].DWMC
                item.FLJBR = flxx.recordset[0].MC ? flxx.recordset[0].MC : '';
            }
            ctx.body = {
                success: true,
                result: result.recordset
            }
        } else {
            ctx.body = { success: false }
        }

    } catch (err) {
        ctx.body = { success: false, err }
    }
}

// 修改发票号
const editInvoiceNumber = async (ctx) => {
    try {
        let { qkdId, FPH } = ctx.request.body;
        let updateSql = `UPDATE QKD_ZB SET FPH = '${FPH}' WHERE ID = '${qkdId}'`;
        let actionResult = await sql.query(updateSql);
        if (actionResult.rowsAffected[0] > 0) {
            ctx.body = {
                success: true
            }
        } else {
            ctx.body = { success: false }
        }

    } catch (err) {
        ctx.body = { success: false, err }
    }
}

module.exports = {
    'POST /getVerifyInvoiceOrder': getVerifyInvoiceOrder,
    'POST /keepVerifyInvoice': keepVerifyInvoice,
    'POST /getVerifyInvoiceOrderDetail': getVerifyInvoiceOrderDetail,
    'POST /keepVerifyInvoiceCancel': keepVerifyInvoiceCancel,
    'POST /getVerifyInvoiceShopDetail': getVerifyInvoiceShopDetail,
    'POST /getPrintVerifyInvoiceInfo': getPrintVerifyInvoiceInfo,
    'POST /getInvoiceExportData': getInvoiceExportData,
    'POST /editInvoiceNumber': editInvoiceNumber

}
